package com.parleys.service;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.parleys.domain.Channel;
import com.parleys.domain.Presentation;
import com.parleys.domain.PresentationAsset;
import com.parleys.domain.Space;
import com.parleys.domain.Speaker;
import com.parleys.helper.ApplicationConstants;
import com.parleys.service.intf.DBAdapter;

public class DBAdapterImpl implements DBAdapter {

	private static final String DROP_TABLE_IF_EXISTS = "DROP TABLE IF EXISTS ";
	
	private static final String KEY_SPACE_ID = "id";
	private static final String KEY_SPACE_NAME = "name";
	private static final String KEY_SPACE_DESCRIPTION = "description";
	private static final String KEY_SPACE_THUMBNAIL = "thumbnail";

	private static final String KEY_CHANNEL_ID = "id";
	private static final String KEY_CHANNEL_SPACDEID = "spaceId";
	private static final String KEY_CHANNEL_NAME = "name";
	private static final String KEY_CHANNEL_DESCRIPTION = "description";
	private static final String KEY_CHANNEL_THUMBNAIL = "thumbnail";

	private static final String KEY_PRESENTATION_ID = "id";
	private static final String KEY_PRESENTATION_CHANNELID = "channelId";
	private static final String KEY_PRESENTATION_NAME = "name";
	private static final String KEY_PRESENTATION_DESCRIPTION = "description";
	private static final String KEY_PRESENTATION_THUMBNAIL = "thumbnail";
	private static final String KEY_PRESENTATION_MP3 = "mp3";

	private static final String KEY_SPEAKER_ID = "id";
	private static final String KEY_SPEAKER_PRESENTATIONID = "presentationId";
	private static final String KEY_SPEAKER_BIO = "bio";
	private static final String KEY_SPEAKER_NAME = "fullname";

	private static final String KEY_ASSET_ID = "id";
	private static final String KEY_ASSET_PRESENTATIONID = "presentationId";
	private static final String KEY_ASSET_DURATION = "duration";
	private static final String KEY_ASSET_CUEPOINT = "cuepoint";
	private static final String KEY_ASSET_SEQUENCE = "sequence";
	private static final String KEY_ASSET_NAME = "name";
	private static final String KEY_ASSET_VALUE = "value";
	private static final String KEY_ASSET_TARGET = "target";
	private static final String KEY_ASSET_TYPE = "type";
	private static final String KEY_ASSET_MP3 = "mp3";

	private static final String DATABASE_NAME = "parleys.db";
	private static final String DATABASE_TABLE_SPACE = "space";
	private static final String DATABASE_TABLE_CHANNEL = "channel";
	private static final String DATABASE_TABLE_PRESENTATION = "presentation";
	private static final String DATABASE_TABLE_SPEAKER = "speaker";
	private static final String DATABASE_TABLE_ASSET = "asset";
	private static final int DATABASE_VERSION = 2;

	private static final String DATABASE_CREATE_SPACE = "create table space (id integer primary key autoincrement, "
			+ "name text not null, description text not null, " + "thumbnail text not null);";

	private static final String DATABASE_CREATE_CHANNEL = "create table channel (id integer primary key autoincrement, "
			+ "spaceId integer not null, name text not null, description text not null, " + "thumbnail text not null);";

	private static final String DATABASE_CREATE_PRESENTATION = "create table presentation (id integer primary key autoincrement, "
			+ "channelId integer not null, name text not null, description text not null, "
			+ "thumbnail text not null, mp3 text not null);";

	private static final String DATABASE_CREATE_SPEAKER = "create table speaker (id integer primary key autoincrement, "
			+ "presentationId integer not null," + "bio text not null, fullname text not null);";

	private static final String DATABASE_CREATE_ASSET = "create table asset (id integer primary key autoincrement, "
			+ "presentationId integer not null, duration real, cuepoint real, sequence integer, "
			+ "name text, value text, target text, type text, mp3 text);";

	private SQLiteDatabase db;

	public DBAdapterImpl() {
	}

	public void openForReading() {
		File dbFile = new File(ApplicationConstants.EXTERNAL_STORAGE_DIRECTORY, DATABASE_NAME);
		db = SQLiteDatabase.openDatabase(dbFile.getAbsolutePath(), null, SQLiteDatabase.CREATE_IF_NECESSARY);
		checkDatabase();
	}
	
	public void openForWriting() {
		File dbFile = new File(ApplicationConstants.EXTERNAL_STORAGE_DIRECTORY, DATABASE_NAME);
		db = SQLiteDatabase.openDatabase(dbFile.getAbsolutePath(), null, SQLiteDatabase.CREATE_IF_NECESSARY+SQLiteDatabase.OPEN_READWRITE);
		checkDatabase();
	}

	public void close() {
		db.close();
	}

	public long insertSpace(final Space space) {
		ContentValues values = new ContentValues();
		values.put(KEY_SPACE_ID, space.getId());
		values.put(KEY_SPACE_NAME, space.getName());
		values.put(KEY_SPACE_DESCRIPTION, space.getDescription());
		values.put(KEY_SPACE_THUMBNAIL, space.getThumbnail());
		return db.insert(DATABASE_TABLE_SPACE, null, values);
	}

	public List<Space> getAllSpaces() {
		Cursor cursor = db.query(DATABASE_TABLE_SPACE, new String[] { KEY_PRESENTATION_ID, KEY_PRESENTATION_NAME,
				KEY_PRESENTATION_DESCRIPTION, KEY_PRESENTATION_THUMBNAIL }, null, null, null, null, null);

		List<Space> list = new ArrayList<Space>();
		while (cursor.moveToNext()) {
			Space space = new Space();
			space.setId(cursor.getInt(0));
			space.setName(cursor.getString(1));
			space.setDescription(cursor.getString(2));
			space.setThumbnail(cursor.getString(3));
			list.add(space);
		}
		cursor.close();
		
		return list;
	}

	public long insertChannelForSpace(final Channel channel, final Integer spaceId) {
		ContentValues values = new ContentValues();
		values.put(KEY_CHANNEL_ID, channel.getId());
		values.put(KEY_CHANNEL_SPACDEID, spaceId);
		values.put(KEY_CHANNEL_NAME, channel.getName());
		values.put(KEY_CHANNEL_DESCRIPTION, channel.getDescription());
		values.put(KEY_CHANNEL_THUMBNAIL, channel.getThumbnail());
		return db.insert(DATABASE_TABLE_CHANNEL, null, values);
	}

	public List<Channel> getAllChannelsForSpace(final Integer spaceId) {
		Cursor cursor = db.query(DATABASE_TABLE_CHANNEL, new String[] { KEY_CHANNEL_ID, KEY_CHANNEL_NAME,
				KEY_CHANNEL_DESCRIPTION, KEY_CHANNEL_THUMBNAIL }, "spaceId=?", new String[] { spaceId.toString() },
				null, null, null);

		List<Channel> list = new ArrayList<Channel>();
		while (cursor.moveToNext()) {
			Channel channel = new Channel();
			channel.setId(cursor.getInt(0));
			channel.setName(cursor.getString(1));
			channel.setDescription(cursor.getString(2));
			channel.setThumbnail(cursor.getString(3));
			channel.setSpaceId(spaceId);
			list.add(channel);
		}
		cursor.close();

		return list;
	}

	public long insertPresentationForChannelId(final Presentation presentation, final Integer channelId) {
		final ContentValues values = new ContentValues();
		values.put(KEY_PRESENTATION_ID, presentation.getId());
		values.put(KEY_PRESENTATION_CHANNELID, channelId);
		values.put(KEY_PRESENTATION_NAME, presentation.getName());
		values.put(KEY_PRESENTATION_DESCRIPTION, presentation.getDescription());
		values.put(KEY_PRESENTATION_THUMBNAIL, presentation.getThumbnail());
		values.put(KEY_PRESENTATION_MP3, presentation.getMp3());
		for (Speaker speaker : presentation.getSpeakers()) {
			insertSpeakerForPresentationId(speaker, presentation.getId());
		}
		return db.insert(DATABASE_TABLE_PRESENTATION, null, values);
	}

	public List<Presentation> getAllPresentationsForChannelId(final Integer channelId) {
		final Cursor cursor = db.query(DATABASE_TABLE_PRESENTATION,
				new String[] { KEY_PRESENTATION_ID, KEY_PRESENTATION_CHANNELID, KEY_PRESENTATION_NAME, KEY_PRESENTATION_DESCRIPTION,
						KEY_PRESENTATION_THUMBNAIL, KEY_PRESENTATION_MP3 }, "channelId=?", new String[] { channelId
						.toString() }, null, null, null);

		List<Presentation> list = parsePresentationFromCursor(cursor);
		cursor.close();

		return list;
	}

	@Override
	public List<Presentation> getDownloadedPresentations() {
		final Cursor cursor = db.query(DATABASE_TABLE_PRESENTATION,
				new String[] { KEY_PRESENTATION_ID, KEY_PRESENTATION_CHANNELID, KEY_PRESENTATION_NAME, KEY_PRESENTATION_DESCRIPTION,
						KEY_PRESENTATION_THUMBNAIL, KEY_PRESENTATION_MP3 }, null, null, null, null, null);

		List<Presentation> list = parsePresentationFromCursor(cursor);
		cursor.close();

		return list;
	}
	
	private List<Presentation> parsePresentationFromCursor(final Cursor cursor) {
		List<Presentation> list = new ArrayList<Presentation>();
		while (cursor.moveToNext()) {
			Presentation presentation = new Presentation();
			presentation.setId(cursor.getInt(0));
			presentation.setChannelId(cursor.getInt(1));
			presentation.setName(cursor.getString(2));
			presentation.setDescription(cursor.getString(3));
			presentation.setThumbnail(cursor.getString(4));
			presentation.setMp3(cursor.getString(5));
			List<Speaker> speakers = getAllSpeakersForPresentationId(presentation.getId());
			presentation.setSpeakers(speakers);
			list.add(presentation);
		}
		return list;
	}

	public boolean isPresentationPresentInDb(final Integer presentationId) {
		final Cursor cursor = db.query(DATABASE_TABLE_PRESENTATION,
				new String[] { KEY_PRESENTATION_ID, KEY_PRESENTATION_NAME, KEY_PRESENTATION_DESCRIPTION,
						KEY_PRESENTATION_THUMBNAIL, KEY_PRESENTATION_MP3 }, "id=?", new String[] { presentationId
						.toString() }, null, null, null);

		Boolean isAvailable = cursor.moveToNext();
		cursor.close();
		return isAvailable;
	}

	private long insertSpeakerForPresentationId(final Speaker speaker, final Integer presentationId) {
		final ContentValues values = new ContentValues();
		values.put(KEY_SPEAKER_ID, speaker.getId());
		values.put(KEY_SPEAKER_PRESENTATIONID, presentationId);
		values.put(KEY_SPEAKER_BIO, speaker.getBio());
		values.put(KEY_SPEAKER_NAME, speaker.getFullname());
		return db.insert(DATABASE_TABLE_SPEAKER, null, values);
	}

	private List<Speaker> getAllSpeakersForPresentationId(final Integer presentationId) {
		final Cursor cursor = db.query(DATABASE_TABLE_SPEAKER, new String[] { KEY_SPEAKER_ID,
				KEY_SPEAKER_PRESENTATIONID, KEY_SPEAKER_BIO, KEY_SPEAKER_NAME }, "presentationId=?",
				new String[] { presentationId.toString() }, null, null, null);

		List<Speaker> list = new ArrayList<Speaker>();
		while (cursor.moveToNext()) {
			Speaker speaker = new Speaker();
			speaker.setId(cursor.getInt(0));
			// speaker.setName(cursor.getString(1));
			speaker.setBio(cursor.getString(2));
			speaker.setFullname(cursor.getString(3));
			list.add(speaker);
		}
		cursor.close();

		return list;
	}

	public long insertAssetForPresentationId(final PresentationAsset asset, final Integer presentationId) {
		final ContentValues values = new ContentValues();
		values.put(KEY_ASSET_ID, asset.getId());
		values.put(KEY_ASSET_PRESENTATIONID, presentationId);
		values.put(KEY_ASSET_DURATION, asset.getDuration());
		values.put(KEY_ASSET_CUEPOINT, asset.getCuepoint());
		values.put(KEY_ASSET_SEQUENCE, asset.getSequence());
		values.put(KEY_ASSET_NAME, asset.getName());
		values.put(KEY_ASSET_VALUE, asset.getValue());
		values.put(KEY_ASSET_TARGET, asset.getTarget());
		values.put(KEY_ASSET_TYPE, asset.getType());
		values.put(KEY_ASSET_MP3, asset.getMp3());
		return db.insert(DATABASE_TABLE_ASSET, null, values);
	}

	public List<PresentationAsset> getAllAssetsForPresentationId(final Integer presentationId) {
		Cursor cursor = db.query(DATABASE_TABLE_ASSET, new String[] { KEY_ASSET_ID, KEY_ASSET_PRESENTATIONID,
				KEY_ASSET_DURATION, KEY_ASSET_CUEPOINT, KEY_ASSET_SEQUENCE, KEY_ASSET_NAME, KEY_ASSET_VALUE,
				KEY_ASSET_TARGET, KEY_ASSET_TYPE, KEY_ASSET_MP3 }, "presentationId=?", new String[] { presentationId
				.toString() }, null, null, null);

		List<PresentationAsset> list = new ArrayList<PresentationAsset>();
		while (cursor.moveToNext()) {
			PresentationAsset asset = new PresentationAsset();
			asset.setId(cursor.getInt(0));
			// speaker.setName(cursor.getString(1));
			asset.setDuration(cursor.getDouble(2));
			asset.setCuepoint(cursor.getDouble(3));
			asset.setSequence(cursor.getInt(4));
			asset.setName(cursor.getString(5));
			asset.setValue(cursor.getString(6));
			asset.setTarget(cursor.getString(7));
			asset.setType(cursor.getString(8));
			asset.setMp3(cursor.getString(9));
			list.add(asset);
		}
		cursor.close();

		return list;
	}
	
	@Override
	public List<Presentation> searchPresentation(String searchString) {
		final Cursor cursor = db.query(DATABASE_TABLE_PRESENTATION,
				new String[] { KEY_PRESENTATION_ID, KEY_PRESENTATION_CHANNELID, KEY_PRESENTATION_NAME, KEY_PRESENTATION_DESCRIPTION,
						KEY_PRESENTATION_THUMBNAIL, KEY_PRESENTATION_MP3 }, KEY_PRESENTATION_NAME + " like ? or "
						+ KEY_PRESENTATION_DESCRIPTION + " like ? ", new String[] { "%"+searchString+"%", "%"+searchString+"%" }, null,
				null, null);

		List<Presentation> list = parsePresentationFromCursor(cursor);
		cursor.close();
		return list;
	}
	
	private void checkDatabase() {
		
		if (db.getVersion() != DATABASE_VERSION) {
			db.execSQL(DROP_TABLE_IF_EXISTS + DATABASE_TABLE_SPACE);
			db.execSQL(DROP_TABLE_IF_EXISTS + DATABASE_TABLE_CHANNEL);
			db.execSQL(DROP_TABLE_IF_EXISTS + DATABASE_TABLE_PRESENTATION);
			db.execSQL(DROP_TABLE_IF_EXISTS + DATABASE_TABLE_SPEAKER);
			db.execSQL(DROP_TABLE_IF_EXISTS + DATABASE_TABLE_ASSET);
			
			db.execSQL(DATABASE_CREATE_SPACE);
			db.execSQL(DATABASE_CREATE_CHANNEL);
			db.execSQL(DATABASE_CREATE_PRESENTATION);
			db.execSQL(DATABASE_CREATE_SPEAKER);
			db.execSQL(DATABASE_CREATE_ASSET);
			db.setVersion(DATABASE_VERSION);
		}
		
	}

}
